<?php
/**
 * Copyright (c) 2006-2008, Julien PORTALIER
 * http://featherphp.googlecode.com/
 *
 * Licensed under The MIT License
 * Redistributions of files must retain the above copyright notice.
 */

/**
 * PostgreSQL backend.
 * 
 * @bug PostgreSQL backend is outdated!
 */
class db_pgsql extends db_base
{
	protected $operators   = '=|<|>|<=|>=|<>|<=>|LIKE|REGEXP|BETWEEN|@@';
	protected $field_quote = '"';

	function start()
	{
		$str = "host='{$this->conf['host']}' user='{$this->conf['login']}' password='{$this->conf['password']}' dbname='{$this->conf['database']}'";
		
		# ignition
		if ($this->conf['permanent'])
		{
			if (false === ($this->link = pg_pconnect($str))) {
				throw new Exception("Can't connect to MySQL server.", DBO::ErrorConnect);
			}
		}
		else
		{
			# forces creation of new link (just in case)
			if (false === ($this->link = pg_connect($str))) {
				throw new Exception("Can't connect to MySQL server.", DBO::ErrorConnect);
			}
		}
	}
	
	function stop()
	{
		if ($this->link)
		{
			pg_close($this->link);
			$this->link = false;
		}
	}
	
	// @todo
	function use_database($database=null)
	{
		//$this->execute("USE $database ;");
	}
	
	function escape($v)
	{
		return pg_escape_string($this->link, $v);
	}
	
	function execute($sql)
	{
		if (!$this->link) {
			$this->start();
		}
		
		# parses SQL
		if (is_array($sql))
		{
			$sql = $this->generate_sql($sql);
		}
		elseif (func_num_args() > 1)
		{
			$args = func_get_args();
			$sql  = call_user_func_array(array($this, 'parse_sql_with_parameters'), $args);
		}
		
		# execute & time request
		$_start = microtime(true);
		$rs     = pg_query($this->link, $sql);
		$_end   = microtime(true);
		
		if (!$rs)
		{
			#. error
			$this->error($sql);
		}
		else if (DEBUG < 2)
		{
			#. slow request?
			if ($_end - $_start >= DBO::SLOW_QUERY and function_exists('db_log_slow_query')) {
				db_log_slow_query($sql, $_end - $_start, $this->ds);
			}
		}
		else
		{
			#. debug
			$this->debug($sql, $_end - $_start);
		}
		return $rs;
	}
	
	protected function & parse_query($rs, $process=DBO::PROCESS_FULL)
	{
		$data = array();
		if ($rs and pg_num_rows($rs) > 0)
		{
			switch($process)
			{
				# data[table][field] = value
				case DBO::PROCESS_FULL:
					while ($row = pg_fetch_row($rs))
					{
						$r = array();
						foreach ($row as $idx => $v)
						{
							$table = pg_field_table($rs, $idx);
							if (isset($this->table_aliases[$table])) {
								$table = $this->table_aliases[$table];
							}
							$r[$table][pg_field_name($rs, $idx)] = $v;
						}
						array_push($data, $r);
					}
#					debug($this->table_aliases);
#					debug($data);
				break;
				
				# data[field] = value
				case DBO::PROCESS_LITE:
					while ($row = pg_fetch_assoc($rs)) {
						array_push($data, $row);
					}
				break;
				
				# value
				case DBO::PROCESS_VALUE:
					$row = pg_fetch_row($rs);
					$data = $row[0];
				break;
				
				# data[index] = value
				case DBO::PROCESS_LIST:
					while ($row = pg_fetch_row($rs)) {
						array_push($data, $row[0]);
					}
				break;
				
				# data[key] = value
				case DBO::PROCESS_ASSOC_LIST:
					while ($row = pg_fetch_row($rs)) {
						$data[$row[0]] = $row[1];
					}
				break;
				
				# data[] = value
				case DBO::PROCESS_ARRAY:
					while ($row = pg_fetch_array($rs)) {
						array_push($data, $row);
					}
				break;
				
				# data[] = value
				case DBO::PROCESS_NONE:
				default:
					while ($row = pg_fetch_row($rs)) {
						array_push($data, $row);
					}
				break;
			}
			
			# cleanup
			pg_free_result($rs);
			unset($this->table_aliases);
		}
		return $data;
	}
	
	function & describe_table($table)
	{
		$fields = array();
		$rs = $this->select("SELECT a.attname AS field, t.typname AS type, a.atttypmod AS length, a.attnotnull AS notnull
			FROM pg_class AS c
			INNER JOIN pg_attribute AS a ON a.attnum > 0 AND a.attrelid = c.oid 
			INNER JOIN pg_type AS t ON a.atttypid = t.oid
			WHERE c.relname = '$table'
			ORDER BY a.attnum ;", DBO::PROCESS_LITE);
		
		foreach($rs as $f)
		{
			$field = $f['field'];
			$fields[$field] = array(
				'type' => $f['type'],
				'null' => ($f['notnull'] == 'f'),
#				'key'  => ($f['pkey'] == 't'),
			);
			if ($f['length'] > 0) {
				$fields[$field]['length'] = $f['length'] - 4;
			}
		}
		return $fields;
	}
	
	function limit($limit, $page=1)
	{
		if ($page > 1) {
			return "LIMIT $limit OFFSET ".(($page - 1) * $limit);
		}
		elseif ($limit) {
			return "LIMIT $limit";
		}
	}
	
	function get_last_insert_id($table, $key)
	{
		return $this->select("SELECT currval(pg_get_serial_sequence('$table', '$key')) ;", DBO::PROCESS_VALUE);
	}
	
	function insert($table, array &$data, $key=null)
	{
		$table  = $this->field($table);
		$fields = $this->fields(array_keys($data));
		$values = $this->values(array_values($data));
		$sql    = "INSERT INTO $table ( $fields ) VALUES ( $values )";
		
		if (!is_null($key))
		{
			$sql .= " RETURNING ".$this->field($key);
			return $this->select("$sql ;", DBO::PROCESS_VALUE);
		}
		else {
			return $this->execute("$sql ;");
		}
	}
	
	/**
	 * REPLACE is unsupported by PGSQL.
	 */
	function replace($table, array &$data)
	{
		trigger_error("db_pgsql::replace() is unsupported.");
	}
	
	/**
	 * REPLACE is unsupported by PGSQL.
	 */
	function replace_many($table, array &$data)
	{
		trigger_error("db_pgsql::replace() is unsupported.");
	}
	
	
	protected function error($sql=null)
	{
		if (DEBUG)
		{
			if (!is_null($sql)) {
				echo '<p class="monospace">'.$sql.'</p>';
			}
			trigger_error("PostgreSQL error ".pg_last_error($this->link), E_USER_ERROR);
		}
		elseif (function_exists('db_log_error')) {
			db_log_error($sql, pg_last_error($this->link), null, $this->ds);
		}
#		else {
#			$this->debug($sql);
#		}
	}
	
	/**
	 * Record the query (when in SQL DEBUG mode).
	 */
	protected function debug($sql, $time=null)
	{
		if (!DEBUG) {
			return;
		}
		$q = array(
			'sql'      => $sql,
			'error'    => pg_last_error($this->link),
			'affected' => /*pg_affected_rows($this->link)*/ null,
			'time'     => $time
		);
		array_push($this->debugs, $q);
	}
}
?>